<?php
class ModelCommunityViewtopic extends Model{
    public function getPostTotal ($getData){
        $encrypt_data=md5($getData['tid']);

        $query=$this->sql_cache->get('viewtopic.posttotal.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$getData['tid'] . "' AND post_deleted='0' AND post_approved='1'");

            $this->sql_cache->set('viewtopic.posttotal.' . $encrypt_data,$query,TRUE);
        }

        return $query->row['total'];
    }

    public function getPostIds ($getData){
        $encrypt_data=md5($getData['tid']);

        $query=$this->sql_cache->get('viewtopic.postids.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT post_id FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$getData['tid'] . "' AND post_deleted='0' AND post_approved='1' ORDER BY post_id ASC");

            $this->sql_cache->set('viewtopic.postids.' . $encrypt_data,$query,TRUE);
        }

        return $query->rows;
    }

    public function getTopicInfo ($getData){
        $encrypt_data=md5($getData['tid']);

        $query=$this->sql_cache->get('viewtopic.topicinfo.' . $encrypt_data. '.' . (int)$this->config->get('config_language_id'));

        if(!$query){
            $query=$this->db->query("SELECT t.forum_id, t.topic_title, t.topic_type, t.topic_first_post_id, t.topic_last_post_id, t.topic_your_day, t.topic_polled, t.topic_solved, t.topic_answer_post_id, t.poll_title, t.poll_max_options, t.cart_product_id, t.poll_start, t.poll_length, t.topic_approved, t.topic_status, fn.forum_name, fpn.forum_name AS parent_forum_name, f.parent_id, f.forum_enabled, pr.model, pr.quantity, pr.image, pr.price, pr.points, pd.name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=t.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fpn ON (fpn.forum_id=f.parent_id AND fpn.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "product AS pr ON (pr.product_id=t.cart_product_id) LEFT JOIN " . DB_PREFIX . "product_description AS pd ON (pd.product_id=t.cart_product_id) WHERE t.topic_id='" . (int)$getData['tid'] . "' AND t.topic_deleted='0' AND topic_approved='1' LIMIT 0,1");

            $this->sql_cache->set('viewtopic.topicinfo.' . $encrypt_data. '.' . (int)$this->config->get('config_language_id'),$query,TRUE);
        }

        return $query->row;
    }

    public function getYourDayNameData($topic_id){
        $encrypt_data=md5($topic_id);

        $query=$this->sql_cache->get('community.yournamenamedata.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT name_id, upcoming_date FROM " . DB_PREFIX . "community_yourday WHERE topic_id='" . (int)$topic_id . "' LIMIT 0,1");

            $this->sql_cache->set('community.yournamenamedata' . $encrypt_data, $query,TRUE);
        }

        return $query->row;
    }

    public function getYourDayParticipants($topic_id){
        $encrypt_data=md5($topic_id);

        $query=$this->sql_cache->get('community.yourdayparticipants.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT user_id, username FROM " . DB_PREFIX . "community_yourday_participant WHERE name_id=(SELECT name_id FROM " . DB_PREFIX . "community_yourday WHERE topic_id='" . (int)$topic_id . "' LIMIT 0,1)");

            $this->sql_cache->set('community.yourdayparticipants.' . $encrypt_data, $query,TRUE);
        }

        return $query->rows;
    }

    public function addVotes($postData,$getData){
        if(is_array($postData['poll_option'])){
            foreach($postData['poll_option'] as $poll_option){
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_poll_votes SET topic_id='" . (int)$getData['tid'] . "', poll_option_id='" . (int)$poll_option . "', vote_user_id='" . (int)$this->customer->getForumUserId() . "', vote_user_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', vote_time='" . (int)time() . "'");

                $this->db->query("UPDATE " . DB_PREFIX . "community_poll_options SET poll_option_total=poll_option_total+1 WHERE topic_id='" . (int)$getData['tid'] . "' AND poll_option_id='" . (int)$poll_option . "'");
            }
        }else{
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_poll_votes SET topic_id='" . (int)$getData['tid'] . "', poll_option_id='" . (int)$postData['poll_option'] . "', vote_user_id='" . (int)$this->customer->getForumUserId() . "', vote_user_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', vote_time='" . (int)time() . "'");

            $this->db->query("UPDATE " . DB_PREFIX . "community_poll_options SET poll_option_total=poll_option_total+1 WHERE topic_id='" . (int)$getData['tid'] . "' AND poll_option_id='" . (int)$postData['poll_option'] . "'");
        }

        $encrypt_data=md5($getData['tid']);
        $this->sql_cache->delete('viewtopic.pollvotestotal.' . $encrypt_data);
        $this->sql_cache->delete('viewtopic.polloptions.' . $encrypt_data);
    }

    public function getPollOptions($getData){
        $encrypt_data=md5($getData['tid']);

        $query=$this->sql_cache->get('viewtopic.polloptions.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT poll_option_id, poll_option_text, poll_option_total FROM " . DB_PREFIX . "community_poll_options WHERE topic_id='" . (int)$getData['tid'] . "' AND topic_deleted='0' ORDER BY poll_option_id ASC");

            $this->sql_cache->set('viewtopic.polloptions.' . $encrypt_data,$query,TRUE,(60*60*24));
        }

        return $query->rows;
    }

    public function getPollVotesTotal($getData){
        $encrypt_data=md5($getData['tid']);

        $query=$this->sql_cache->get('viewtopic.pollvotestotal.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT count(*) AS total_votes FROM " . DB_PREFIX . "community_poll_votes WHERE topic_id='" . (int)$getData['tid'] . "' AND topic_deleted='0'");

            $this->sql_cache->set('viewtopic.pollvotestotal.' . $encrypt_data,$query);
        }

        return $query->row['total_votes'];
    }

    public function getUserVotes($getData){
        $query=$this->db->query("SELECT poll_option_id FROM " . DB_PREFIX . "community_poll_votes WHERE topic_id='" . (int)$getData['tid'] . "'AND topic_deleted='0' AND vote_user_id='" . (int)$this->customer->getForumUserId() . "'");

        return $query->rows;
    }

    public function getIpLastVoteTime($getData){
        $encrypt_data=md5($getData['tid'] . '.' . $this->request->server['REMOTE_ADDR']);

        $query=$this->sql_cache->get('viewtopic.iplastvotetime.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT vote_time FROM " . DB_PREFIX . "community_poll_votes WHERE topic_id='" . (int)$getData['tid'] . "' AND topic_deleted='0' AND vote_user_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "' LIMIT 0,1");

            $this->sql_cache->set('viewtopic.iplastvotetime.' . $encrypt_data,$query, TRUE, ($this->config->get('poll_ip_vote_time_limit')-2));
        }

        return $query->row;
    }

    public function getPosts($getData,$start,$limit){
        $encrypt_data=md5($getData['tid']);

        $results=$this->sql_cache->get('viewtopic.posts.' . $encrypt_data . '.' . $start . '-' . $limit . '.' . $this->config->get('config_language_id'));

        if(!$results){
            $results=$this->db->query("SELECT f.forum_id, p.*, tr.user_id AS rater_id, tr.username AS rater_name, tr.rating, tr.date_added AS rating_date, u.user_id, u.user_thanks_given, u.user_thanks_received, u.user_sig, u.user_avatar, u.user_posts, u.user_best_answers, u.user_regdate, u.user_allow_pm, u.user_allow_email, u.user_from, u.user_email, u.user_rank, eu.username AS editor_user_name, pe.posting_bbcode, pe.posting_bbcode_url, pe.posting_bbcode_img, pe.posting_bbcode_flash, pe.posting_smiles, rt.role_title, rt.role_image, r.role_id, r.allow_user_sig, r.allow_user_sig_bbcode, r.allow_user_sig_bbcode_img, r.allow_user_sig_bbcode_flash, r.allow_user_sig_bbcode_url, r.allow_user_sig_smiles, ra.*, rat.rank_title, rat.rank_image FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topic_review AS tr ON(tr.topic_id=p.topic_id AND tr.post_id=p.post_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON(p.poster_id=u.user_id) LEFT JOIN " . DB_PREFIX . "community_users AS eu ON(p.post_edit_user=eu.user_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON(p.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_permission AS pe ON (pe.forum_id=p.forum_id AND pe.role_id=u.user_type AND pe.rank_id=IF(u.user_type=5,u.user_rank,'0')) LEFT JOIN " . DB_PREFIX . "community_role AS r ON (r.role_id=u.user_type AND r.rank_id=pe.rank_id) LEFT JOIN " . DB_PREFIX . "community_role_title AS rt ON (rt.role_id=r.role_id AND rt.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "community_ranks AS ra ON (ra.rank_id=pe.rank_id) LEFT JOIN " . DB_PREFIX . "community_rank_title AS rat ON (rat.rank_id=ra.rank_id AND rat.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE p.topic_id=" . $getData['tid'] . " AND p.post_approved='1' AND p.post_deleted='0' ORDER BY p.post_time ASC LIMIT " . $start . "," . $limit);

            $this->sql_cache->set('viewtopic.posts.' . $encrypt_data . '.' . $start . '-' . $limit . '.' . $this->config->get('config_language_id'), $results, TRUE);
        }

        $poster_id_str='';

        foreach($results->rows as $result){
            $poster_id_str.=$result['poster_id'] . ',';
        }

        $poster_id_str=rtrim($poster_id_str,',');

        $session_results=$this->db->query("SELECT * FROM " . DB_PREFIX . "community_sessions WHERE session_user_id IN (" . $poster_id_str . ")");

        $query=array();

        $i=0;

        foreach($results->rows as $result){
            $query[]=$result;

            foreach($session_results->rows as $session_result){
                if($session_result['session_user_id']==$result['poster_id']){
                    $query[$i]['session_last_visit_time']=$session_result['session_last_visit_time'];
                    $query[$i]['session_viewonline']=$session_result['session_viewonline'];
                }
            }
            $i++;
        }

        return $query;
    }

    public function getNextRank($rank_id){
        $encrypt_data=md5($rank_id);

        $query=$this->sql_cache->get('viewtopic.nextrank.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query("SELECT rank_min FROM " . DB_PREFIX . "community_ranks WHERE rank_id>'" . (int)$rank_id . "' ORDER BY rank_id DESC LIMIT 0,1");

            $this->sql_cache->set('viewtopic.nextrank.' . $encrypt_data,$query);
        }

        return $query->row;
    }

    //update forum track
    public function updateForumTrack($getData,$page_total){
        $getData['page']=isset($getData['page']) ? $getData['page'] : 1;

        if($page_total==$getData['page'] && $this->customer->isLogged()){
            $this->db->query("SELECT 1 FROM " . DB_PREFIX . "community_forum_track WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "' LIMIT 0,1");

            if($this->db->countAffected()){
                $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topics WHERE forum_id='" . (int)$getData['fid'] . "' AND topic_last_post_time>='" . (int)(time()-$this->config->get('topics_be_read_time')) . "' AND topic_deleted='0' AND topic_approved='1'");
                $topicNum=$query->row['total'];

                $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topics_track AS tt LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=tt.topic_id) WHERE tt.forum_id='" . (int)$getData['fid'] . "' AND tt.user_id='" . (int)$this->customer->getForumUserId() . "' AND tt.mark_time>t.topic_last_post_time");
                $topicTrackNum=$query->row['total'];

                if($topicTrackNum>=$topicNum){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_forum_track SET mark_time='" . (int)time() . "' WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "'");
                }
            }else{
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_forum_track SET user_id='" . (int)$this->customer->getForumUserId() . "', forum_id='" . (int)$getData['fid'] . "', mark_time='" . (int)time() . "'");
            }
        }
    }

    //update topic track
    public  function updateTopicTrack($getData,$page_total){
        $getData['page']=isset($getData['page']) ? $getData['page'] : 1;

        if($page_total==$getData['page']){
            $query=$this->db->query("SELECT t.topic_last_post_time, tt.mark_time FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_topics_track AS tt ON (t.topic_id=tt.topic_id AND tt.user_id='" . (int)$this->customer->getForumUserId() . "') WHERE t.topic_id='" . $getData['tid'] . "' LIMIT 0,1");

            $topic_last_post_time=$query->row['topic_last_post_time'];
            $mark_time=$query->row['mark_time'];

            if((time()-$topic_last_post_time<=(int)$this->config->get('topics_be_read_time') && $this->customer->isLogged())){
                if(isset($mark_time)){
                    if($mark_time<=$topic_last_post_time){
                        $this->db->query("UPDATE " . DB_PREFIX . "community_topics_track SET mark_time='" . (int)time() . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND topic_id='" . (int)$getData['tid'] . "'");
                    }
                }else{
                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_topics_track SET user_id='" . (int)$this->customer->getForumUserId() . "', topic_id='" . (int)$getData['tid'] . "', forum_id='" . (int)$getData['fid'] . "', mark_time='" . (int)time() . "'");
                }
            }
        }
    }

    public function updateViewCount($getData){
        $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_views=topic_views+1, topic_last_view_time='" . (int)time() . "' WHERE topic_id='" . $getData['tid'] . "'");
    }

    public function updateTopicNotification($getData){
        $query=$this->db->query("SELECT topic_notified FROM " . DB_PREFIX . "community_topic_notify WHERE topic_id='" . (int)$getData['tid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "' LIMIT 0,1");

        if(isset($query->row['topic_notified']) && $query->row['topic_notified']==1){
            $this->db->query("UPDATE " . DB_PREFIX . "community_topic_notify SET topic_notified='0' WHERE topic_id='" . (int)$getData['tid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "'");
        }
    }

    public function updateForumPostNotification($getData){
        $query=$this->db->query("SELECT notify_status FROM " . DB_PREFIX . "community_forum_notify WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "' LIMIT 0,1");

        if(isset($query->row['notify_status']) && $query->row['notify_status']==1){
            $this->db->query("UPDATE " . DB_PREFIX . "community_forum_notify SET notify_status='0' WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "'");
        }
    }

    public function softDeleteOwnPost($topicData,$postData,$getData){
        if($topicData['topic_first_post_id']==$postData['pid']){// if topic has only one post
            $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=t.topic_first_post_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify As tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET t.topic_deleted='1', p.post_deleted='1', u.user_topics=u.user_topics-1, u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, f.forum_topics=f.forum_topics-1, po.topic_deleted='1', pv.topic_deleted='1', tn.topic_deleted='1', b.topic_deleted='1', stt.`value`=stt.`value`-1, stp.`value`=stp.`value`-1 WHERE t.topic_id='" . (int)$getData['tid'] . "'");

            //clear sql cache
            $encrypt_data=md5($getData['tid']);
            $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
            $this->sql_cache->delete('community.foruminfo.');

            if($topicData['topic_type']>=2){
                $this->sql_cache->delete('viewforum.announcementtopics.' . md5($getData['fid']));
            }

            $this->sql_cache->delete('viewforum.topictotal.' . md5($getData['fid']));
            $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));

            return 'topic_deleted';
        }else{
            $postLastButOneData=$this->getLastButOnePost($getData);

            $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS s ON (s.`key`='total_posts') SET p.post_deleted='1', u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, t.topic_replies=t.topic_replies-1, t.topic_last_post_id='" . (int)$postLastButOneData['post_id'] . "', t.topic_last_poster_id='" . (int)$postLastButOneData['poster_id'] . "', t.topic_last_poster_name='" . $this->db->escape($postLastButOneData['post_username']) . "', t.topic_last_post_subject='" . $this->db->escape($postLastButOneData['post_subject']) . "', topic_last_post_time='" . $this->db->escape($postLastButOneData['post_time']) . "', s.`value`=s.`value`-1 WHERE p.post_id='" . (int)$postData['pid'] . "'");

            //clear sql cache
            $encrypt_data=md5($getData['tid']);
            $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
            $this->sql_cache->delete('community.foruminfo.');

            return 'post_deleted';
        }
    }

    public function moderatorSoftDeletePost($topicData,$postData,$getData){
        if($topicData['topic_first_post_id']==$postData['pid']){// if delete the whole topic
            $results=$this->getAllPostInfo($getData);

            $i=0;
            $post_id_str='';

            foreach($results as $result){
                $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_posts=user_posts-1 WHERE user_id='" . (int)$result['poster_id'] . "'");

                $post_id_str.=$result['post_id'] . ',';

                $i++;
            }

            $post_id_str=rtrim($post_id_str,',');

            $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id IN (" . $post_id_str . ")) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS tu ON (tu.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify AS tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET p.post_deleted='1', t.topic_deleted='1', f.forum_topics=f.forum_topics-1, f.forum_posts=f.forum_posts-" . (int)$i . ", tu.user_topics=tu.user_topics-1, po.topic_deleted='1', pv.topic_deleted='1', tn.topic_deleted='1', b.topic_deleted='1', stt.`value`=stt.`value`-1, stp.`value`=stp.`value`-" . (int)$i . " WHERE t.topic_id='" . (int)$getData['tid'] . "'");

            //add moderator log
            $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_id='" . (int)$getData['tid'] . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$getData['tid'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");

            //clear sql cache
            $encrypt_data=md5($getData['tid']);
            $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
            $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
            $this->sql_cache->delete('viewforum.topictotal.' . md5($getData['fid']));
            $this->sql_cache->delete('community.foruminfo.');

            if($topicData['topic_type']>=2){
                $this->sql_cache->delete('viewforum.announcementtopics.' . md5($getData['fid']));
            }

            return 'topic_deleted';
        }elseif($topicData['topic_last_post_id']==$postData['pid']){//if delete the last post in a topic

            $postLastButOneData=$this->getLastButOnePost($getData);

            $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS s ON (s.`key`='total_posts') SET p.post_deleted='1', u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, t.topic_replies=t.topic_replies-1, t.topic_last_post_id='" . (int)$postLastButOneData['post_id'] . "', t.topic_last_poster_id='" . (int)$postLastButOneData['poster_id'] . "', t.topic_last_poster_name='" . $this->db->escape($postLastButOneData['post_username']) . "', t.topic_last_post_subject='" . $this->db->escape($postLastButOneData['post_subject']) . "', topic_last_post_time='" . $this->db->escape($postLastButOneData['post_time']) . "', s.`value`=s.`value`-1 WHERE p.post_id='" . (int)$postData['pid'] . "'");

            //add moderator log
            $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE p.post_id='" . (int)$postData['pid'] . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");

            //clear viewtopic sql cache
            $encrypt_data=md5($getData['tid']);
            $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
            $this->sql_cache->delete('community.foruminfo.');

            return 'post_deleted';
        }else{
            $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS s ON (s.`key`='total_posts') SET p.post_deleted='1', u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, t.topic_replies=t.topic_replies-1, s.`value`=s.`value`-1 WHERE p.post_id='" . (int)$postData['pid'] . "'");

            //add moderator log
            $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE p.post_id='" . (int)$postData['pid'] . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");

            //clear viewtopic sql cache
            $encrypt_data=md5($getData['tid']);
            $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
            $this->sql_cache->delete('community.foruminfo.');

            return 'post_deleted';
        }
    }

    private function getLastButOnePost($getData){
        $query=$this->db->query("SELECT post_id, poster_id, post_time, post_username, post_subject FROM " . DB_PREFIX . "community_posts WHERE topic_id=" . (int)$getData['tid'] . " AND post_approved='1' AND post_deleted='0' ORDER BY post_time DESC LIMIT 1,1 ");
        return $query->row;
    }

    private function getAllPostInfo($getData){
        $query=$this->db->query("SELECT post_id, poster_id FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$getData['tid'] . "' AND post_deleted='0' ORDER BY post_time DESC");
        return $query->rows;
    }

    //update forum new post notification record
    public function updateForumNotification($getData){
        $query=$this->db->query("SELECT notify_status FROM " . DB_PREFIX . "community_forum_notify WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "' LIMIT 0,1");

        if(isset($query->row['notify_status']) && $query->row['notify_status']==1){
            $this->db->query("UPDATE " . DB_PREFIX . "community_forum_notify SET notify_status='0' WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "'");
        }
    }

    //subscribe topic
    public function subsribeTopic($getData){
        $this->db->query("INSERT INTO " . DB_PREFIX . "community_topic_notify SET user_id='" . (int)$this->customer->getForumUserId() . "', topic_id='" . (int)$getData['tid'] . "', topic_notified='0'");
    }

    public function DetectSubsribeTopic($getData){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topic_notify WHERE topic_id='" . (int)$getData['tid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "' LIMIT 0,1");

        return $query->row['total'];
    }

    public function unsubscribeTopic($getData){
         $this->db->query("DELETE FROM " . DB_PREFIX . "community_topic_notify WHERE topic_id='" . (int)$getData['tid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    //bookmark topic
    public function bookmarkTopic($getData){
        $this->db->query("INSERT INTO " . DB_PREFIX . "community_bookmarks SET user_id='" . (int)$this->customer->getForumUserId() . "', topic_id='" . (int)$getData['tid'] . "'");
    }

    public function DetectTopicBookmark($getData){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_bookmarks WHERE topic_id='" . (int)$getData['tid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "' LIMIT 0,1");

        return $query->row['total'];
    }

    public function TopicBookmarkRemove($getData){
        $this->db->query("DELETE FROM " . DB_PREFIX . "community_bookmarks WHERE topic_id='" . (int)$getData['tid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    public function MarkTopicSolved($postData,$getData){
        $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t, " . DB_PREFIX . "community_users AS u SET t.topic_solved='1', t.topic_answer_post_id='" . (int)$postData['pid'] . "', u.user_best_answers=u.user_best_answers+1 WHERE t.topic_id='" . (int)$getData['tid'] . "' AND u.user_id='" . (int)$postData['uid'] . "'");

        //detect slave account before adding forum points
        $master_slave_query=$this->db->query("SELECT master_slave FROM " . DB_PREFIX . "customer WHERE customer_id=(SELECT customer_id FROM " . DB_PREFIX . "community_users WHERE user_id='" . (int)$postData['uid'] . "') LIMIT 0,1");

        if($master_slave_query->row['master_slave']!='sl'){
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_forum_point_history SET user_id='" . (int)$postData['uid'] . "', type='3', description='" . $this->db->escape('Best Answer Post Awarded Forum Points') . "', points='" . (int)$this->config->get('best_answer_awarded_forum_points') . "', add_time='" . time() . "'");

            $this->db->query("UPDATE " . DB_PREFIX . "community_users SET forum_points=forum_points+" . (int)$this->config->get('best_answer_awarded_forum_points') . " WHERE user_id='" . (int)$postData['uid'] . "'");
        }

        if($this->customer->getForumUserType()<=3){
            //add moderator log
            $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_id='" . (int)$getData['tid'] . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_solve', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$getData['tid'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");
        }

        //clear viewtopic sql cache
        $encrypt_data=md5($getData['tid']);
        $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
    }

    public function MarkTopicUnsolved($getData){
        $query=$this->db->query("SELECT poster_id FROM " . DB_PREFIX . "community_posts WHERE post_id=(SELECT topic_answer_post_id FROM " . DB_PREFIX . "community_topics WHERE topic_id='" . (int)$getData['tid'] . "' LIMIT 0,1) LIMIT 0,1");

        $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t, " . DB_PREFIX . "community_users AS u SET t.topic_solved='0', t.topic_answer_post_id='0', u.user_best_answers=u.user_best_answers-1 WHERE t.topic_id='" . (int)$getData['tid'] . "' AND u.user_id='" . (int)$query->row['poster_id'] . "'");

        //add moderator log
        if($this->customer->getForumUserType()<=3){
            $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_id='" . (int)$getData['tid'] . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_unsolve', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$getData['tid'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");
        }

        //clear viewtopic sql cache
        $encrypt_data=md5($getData['tid']);
        $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
    }

    public function addThanks($postData){
        $query=$this->db->query("SELECT thanks_giver_usernames, thanks_giver_ids FROM " . DB_PREFIX . "community_posts WHERE post_id='" . (int)$postData['pid'] . "' LIMIT 0,1");

        if(!empty($query->row['thanks_giver_usernames']) && !empty($query->row['thanks_giver_ids'])){
            $user_name_arr=unserialize($query->row['thanks_giver_usernames']);
            $user_id_arr=unserialize($query->row['thanks_giver_ids']);

            array_unshift($user_name_arr,$this->db->escape($this->customer->getForumUserName()));
            array_unshift($user_id_arr,$this->customer->getForumUserId());
        }else{
            $user_name_arr[]=$this->db->escape($this->customer->getForumUserName());
            $user_id_arr[]=(int)$this->customer->getForumUserId();
        }

        $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p, " . DB_PREFIX . "community_users AS u SET p.thanks_received=p.thanks_received+1, p.thanks_giver_usernames='" . serialize($user_name_arr) . "', p.thanks_giver_ids='" . serialize($user_id_arr) . "', u.user_thanks_received=u.user_thanks_received+1 WHERE p.post_id='" . (int)$postData['pid'] . "' AND u.user_id='" . (int)$postData['uid'] . "'");

        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_thanks_given=user_thanks_given+1 WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");

        //clear viewtopic sql cache
        $encrypt_data=md5($this->request->get['tid']);
        $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
    }

    public function addPostRating($postDate,$getData){
        $query=$this->db->query("SELECT * FROM " . DB_PREFIX . "community_topic_review WHERE topic_id='" . (int)$getData['tid'] . "' AND post_id='" . (int)$postDate['pid'] . "' LIMIT 0,1");

        $user_id_array=$query->row ? unserialize($query->row['user_id']) : array();
        $username_array=$query->row ? unserialize($query->row['username']) : array();
        $rating_array=$query->row ? unserialize($query->row['rating']) : array();
        $date_added_array=$query->row ? unserialize($query->row['date_added']) : array();

        if($user_id_array){
            array_push($user_id_array, $this->customer->getForumUserId());
        }else{
            $user_id_array[] = $this->customer->getForumUserId();
        }

        if($username_array){
            array_push($username_array, $this->db->escape($this->customer->getForumUserName()));
        }else{
            $username_array[] = $this->db->escape($this->customer->getForumUserName());
        }

        if($rating_array){
            array_push($rating_array, (int)$postDate['rating']);
        }else{
            $rating_array[] = (int)$postDate['rating'];
        }

        if($date_added_array){
            array_push($date_added_array, time());
        }else{
            $date_added_array[] = time();
        }

        if($query->row){
            $this->db->query("UPDATE " . DB_PREFIX . "community_topic_review SET user_id='" . $this->db->escape(serialize($user_id_array)) . "', username='" . $this->db->escape(serialize($username_array)) . "', rating='" . $this->db->escape(serialize($rating_array)) . "', date_added='" . $this->db->escape(serialize($date_added_array)) . "' WHERE topic_id='" . (int)$getData['tid'] . "'AND post_id='" . (int)$postDate['pid'] . "'");
        }else{
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_topic_review SET topic_id='" . (int)$getData['tid'] . "', post_id='" . (int)$postDate['pid'] . "', user_id='" . $this->db->escape(serialize($user_id_array)) . "', username='" . $this->db->escape(serialize($username_array)) . "', rating='" . $this->db->escape(serialize($rating_array)) . "', date_added='" . $this->db->escape(serialize($date_added_array)) . "'");
        }

        //clear sql cache
        $encrypt_data=md5($getData['tid']);
        $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
    }

    public function getPostRating($topic_id,$post_id){
        $query=$this->db->query("SELECT user_id, username, rating, date_added FROM " . DB_PREFIX . "community_topic_review WHERE topic_id='" . (int)$topic_id . "' AND post_id='" . (int)$post_id . "'");

        $user_name_array=unserialize($query->row['username']);
        $user_id_array=unserialize($query->row['user_id']);
        $rating_array=unserialize($query->row['rating']);
        $date_added_array=unserialize($query->row['date_added']);

        $i=0;
        $data=array();

        foreach($user_id_array as $user_id){
            $data[$i]['user_link']=$this->url->link('community/profile','uid=' . $user_id);
            $data[$i]['username']=$user_name_array[$i];
            $data[$i]['rating']=$rating_array[$i];
            $data[$i]['date_added']=date($this->language->get('date_time_format_short'),$date_added_array[$i]);

            $i++;
        }

        return $data;
    }

    public function addYourDayParticipant($postData,$getData){
        $this->db->query("INSERT INTO " . DB_PREFIX . "community_yourday_participant SET name_id='" . $postData['name-id'] . "', user_id='" . (int)$this->customer->getForumUserId() . "', customer_id='" . (int)$this->customer->getId() . "', customer_group_id='" . (int)$this->customer->getCustomerGroupId() . "', address_id='" . (int)$this->customer->getAddressId() . "', username='" . $this->db->escape($this->customer->getForumUserName()) . "', user_email='" . $this->db->escape($this->customer->getEmail()) . "'");


        $encrypt_data=md5($getData['tid']);
        $this->sql_cache->delete('community.yourdayparticipants.' . $encrypt_data);
    }

    public function validateYourDayClaimer($postData){
        $participant_query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_yourday_participant WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND name_id='" . (int)$postData['name-id'] . "'");

        if($participant_query->row['total']){
            return 'claimed';
        }

        $name_query=$this->db->query("SELECT name FROM " . DB_PREFIX . "community_yourday WHERE name_id='" . (int)$postData['name-id'] . "'");

        if(utf8_strtolower($this->customer->getFirstName())!=utf8_strtolower($name_query->row['name'])){
            return 'wrong_name';
        }

        return TRUE;
    }
}
?>